Use of Web SQL in Phonegap
Posted By : Avilash Choudhary | 01-Sep-2014
In Phonegap, you can use Web SQL to store data on local device. you can perform most of sql queries. To start with database, the first method is openDatabase that will take the four arguments. first is database name then version number,text description and estimated size of database. other method are transaction and executeSql.
Transaction method is used so that you can rollback your queries. With the help of transaction object you can call executeSql and perform any insert, update and delete queries.
This function will return the instance of database to perform sql queries.
db.openDB = function() { var lyfelineDB = window.openDatabase("lyfeline", "1.0", "lyfeline", 200000); lyfelineDB.transaction(function(transaction) { transaction.executeSql("CREATE TABLE IF NOT EXISTS POSTQUESTIONDATA (ID INTEGER PRIMARY KEY,DATA TEXT)"); }); return lyfelineDB; };
To perform insert query you need to write code like this:
db.insertPostQuestionData = function(item) { var lyfelineDB = db.openDB(); lyfelineDB.transaction(function(transaction) { transaction.executeSql('INSERT INTO POSTQUESTIONDATA(ID,DATA) VALUES(?,?)', [JSON.stringify(item.id), JSON.stringify(item)], function(transaction, results) { // success }, function(e) { console.log("some error inserting data"); }); }); };
In executeSql function, first you need to pass sql query then an array with value or blank array then success callback and failure call back.The success callback receives the transanction and result objects. The results object contains rows object which has length. To access the data you need to use results.rows.item(i) where i represents the index of row.
The Sql query to fetch data from database:
db.getQuestionInboxData = function() { var lyfelineDB = db.openDB(); lyfelineDB.transaction(function(transaction) { transaction.executeSql('SELECT ID,DATA FROM POSTQUESTIONDATA', [], function(transaction, results) { var item = JSON.parse(results.rows.item(i).DATA); }, function(e) { console.log("some error getting questions"); }); }); };
transaction.executeSql('SELECT DATA FROM POSTQUESTIONDATA WHERE ID=?', [postId], function(transaction, results) { },function(e){ });
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Avilash Choudhary
Avilash has excellent experience in developing mobile and web applications using jQuery , Javascript and PhoneGap. His hobbies are watching and playing cricket.